﻿using Spire.Xls;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Dotnet.Code
{
    public class SpireExcel:ExcelDocument
    {   
        public SpireExcel(string fileName) : base(fileName) { }

        private Workbook LoadExcelFile(string password="")
        {
            Workbook workbook = new Workbook();
            if (string.IsNullOrEmpty(password) == false)
            {
                workbook.OpenPassword = password;//文档密码
            }
            workbook.LoadFromFile(FileName);//加载excel文档
            if (workbook.Worksheets.Count == 0)
                return null;
            return workbook;
            //Worksheet sheet = workbook.Worksheets[0];
        }

        public override DataTable ExcelToDataTable(string password = "")
        {
            DataTable dt = new DataTable();
            object xxx = new object();
            try
            {
                Workbook workbook = LoadExcelFile(password);
                if (workbook.Worksheets.Count == 0) return null;
                //表头
                CellRange header = workbook.Worksheets[0].Rows[0];
                List<int> columns = new List<int>();
                for (int i = 0; i < header.LastColumn; i++)
                {
                    string colName = header.Cells[i].DisplayedText.Trim();
                    if (string.IsNullOrEmpty(colName) == true)
                        dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
                    else
                        dt.Columns.Add(new DataColumn(colName.ToString()));
                    columns.Add(i);
                }

                //数据
                foreach (Worksheet wbSheet in workbook.Worksheets)
                {
                    int rows = wbSheet.LastRow - 1;
                    for (int i = wbSheet.FirstRow; i <= rows; i++)
                    {
                        if (wbSheet.Name == "2022")
                        {

                        }
                        DataRow dr = dt.NewRow();
                        bool hasValue = false;
                        // int emptyValueCount = 0;//单元格空值记数，当空值==列数时表明该行为空行
                        foreach (int j in columns)
                        {
                            CellRange cell = wbSheet.Rows[i].Cells[j];
                            
                            xxx = cell;
                            bool isFormulaDateTimeExce = true;
                            string cellValue = "";
                            try
                            {
                                cellValue = cell?.DateTimeValue.ToString("yyyy-MM-dd HH:mm:ss");
                                if (cellValue.StartsWith("0001")) isFormulaDateTimeExce = false;
                            }
                            catch { isFormulaDateTimeExce = false; }

                            if (isFormulaDateTimeExce == false) //非时间串
                            {
                                cellValue = cell.DisplayedText.Trim();
                            }
                            if (cellValue.Contains("￥")) cellValue = cellValue.Replace("￥", "");
                            if (cellValue.Contains("¥")) cellValue = cellValue.Replace("¥", "");
                            if (cellValue.Contains("\n")) cellValue = cellValue.Replace("\n", "");
                            if (cellValue.Contains("\r")) cellValue = cellValue.Replace("\r", "");
                            dr[j] = cellValue.TrimStart(new char[] { '￥', '¥' }).Trim();
                            if (string.IsNullOrEmpty(cellValue) == false)
                            {
                                hasValue = true;
                            }
                            Console.WriteLine($"i={i}  j={j} sheet:{wbSheet.Name} {cellValue}");
                        }
                        if (hasValue)
                        {
                            dt.Rows.Add(dr);
                            OnExcelToTable(new ExcelToTableArgs()
                            {
                                SheetRowsCount = rows,
                                SheetCount = workbook.Worksheets.Count,
                                Index = i,
                                CurrDataRow = dr,
                                CurrDataTable = dt,
                                CurrWorkSheet = wbSheet
                            }) ;
                        }
                    }
                }
                return dt;
            }
            catch (Exception ex )
            {
                object xxxxx = xxx;
                //throw;
                return null;
            }
        }
    }
}
